﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Xml;

using Microsoft.AnalysisServices.AdomdClient;

using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Security;

namespace JobBIVerify.Features.JobBIVerify
{
    [Guid("45FADF26-18CE-4A6C-A18A-6D7BD9A7A55A")]
    public class JobBIVerify_v2 : SPJobDefinition
    {
        #region Propertys
        private string _DisplayName =
@"(Ko) JobBIVerify, ver. 1.0.0.3";
        public override string DisplayName
        {
            get
            {
                return _DisplayName;
            }
        }
        private string _Description =
@"(Ko) JobBIVerify, ver. 1.0.0.3";
        public override string Description
        {
            get
            {
                return _Description;
            }
        }
        #endregion Propertys
        #region Members
        string _connStr = "";
        #endregion Members
        #region Constructors
        public JobBIVerify_v2() : base()
        {
            this.Title = "(Ko) Job BI Verify v2";
        }
        public JobBIVerify_v2(string jobName, SPService service, SPServer server, SPJobLockType targetType)
            : base(jobName, service, server, targetType)
        {
            this.Title = "(Ko) Job BI Verify v2";
        }
        public JobBIVerify_v2(string jobName, SPWebApplication webApplication)
            : base(jobName, webApplication, null, SPJobLockType.ContentDatabase)
        {
            this.Title = "(Ko) Job BI Verify v2";
        }
        #endregion Constructors
        #region Common Methods
        public string AllTrim(String mStr)
        {
            String vStr = mStr.Replace(" ", string.Empty);
            return vStr;
        }
        public string ConverDate(String mDate)
        {
            String vDate = "";
            //--
            int mCMP = 0;
            if ((mCMP = mDate.IndexOf(".")) != -1)
            {
                vDate = mDate.Substring(6, 4) + "-" + mDate.Substring(3, 2) + "-" + mDate.Substring(0, 2);
            }
            else
            {
                vDate = mDate;
            }
            //--
            return vDate;
        }
        public string InValue(object mValue)
        {
            String mStr;
            if (mValue == null)
                mStr = "";
            else
                mStr = mValue.ToString();
            return mStr;
        }
        static public string GetAppSetting(String mName)
        {
            String vStr = null;
            SPWebService mSer = SPWebService.ContentService;
            #region Ver 1
            if (mSer != null)
            {
                foreach (SPWebConfigModification mMod in mSer.WebConfigModifications)
                {
                    if (mMod.Owner == mName)
                        vStr = mMod.Value;
                }
            }
            if (vStr != null)
            {
                vStr =
                    vStr.Substring(
                        vStr.IndexOf("value=") + 7
                        , vStr.Length - 7 - vStr.IndexOf("value=") - 4);
            }
            #endregion Ver 1
            else
            #region Ver 2
            {
                string strPath =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(strPath);
                XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
                foreach (XmlNode keynode in node.ChildNodes)
                {
                    string keyStr = keynode.Attributes["key"].Value;
                    if (keyStr == mName)
                    {
                        vStr = keynode.Attributes["value"].Value;
                        break;
                    }
                }
            }
            #endregion Ver 2
            if (String.IsNullOrEmpty(vStr))
                vStr = "";
            return vStr;
        }
        #endregion Common Methods
        #region Job Methods
        public override void Execute(Guid contentDbId)
        {
            _connStr = GetAppSetting(@"BIConnect");

            TimeSpan ts = new TimeSpan(24, 0, 0);
            DateTime dt = DateTime.Now - ts;
            int Year = dt.Year;
            int Month = dt.Month;
            int Day = dt.Day;

            Exception err1 = null;
            Exception err2 = null;
            CellSet cst1 = null;
            CellSet cst2 = null;
            int ShopCount1 = 0, ShopCount2 = 0;
            Decimal Amount1 = 0, Amount2 = 0;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                err1 = GetKPIData(ref cst1, ref ShopCount1, ref Amount1, Year, Month, Day);
                err2 = GetCheckData(ref cst2, ref ShopCount2, ref Amount2, Year, Month, Day);
            });


            int ErrCount = 0;
            int WarningsCount = 0;
            bool bSuccess = true;
            string EventTymeStr = "Job BI Sync";
            string LogTitle = @"Данные подтверждены.";
            StringBuilder inf = new StringBuilder();
            StringBuilder notes = new StringBuilder();
            if (err1 != null)
            {
                ErrCount++; bSuccess = false;
                if (ErrCount > 0)
                {
                    if (ErrCount == 1)
                        LogTitle = "";
                    else
                        LogTitle += "; ";
                    LogTitle += "Данные не подтверждены."; // Ошибка - Невозможно получить данные из куба KPI
                }
                else
                    LogTitle = "Данные не подтверждены.";
            }
            if (err2 != null)
            {
                ErrCount++; bSuccess = false;
                if (ErrCount > 0)
                {
                    if (ErrCount == 1)
                        LogTitle = "";
                    else
                        LogTitle += "; ";
                    LogTitle += "Данные не подтверждены."; // Ошибка - Невозможно получить данные из куба Чеки
                }
                else
                    LogTitle = "Данные не подтверждены.";
            }


            inf.Append(" ; </br>Кол-во магазинов в кубе KPI - ");
            if (ShopCount1 != null)
                inf.Append(ShopCount1.ToString());
            inf.Append(" ; </br>Кол-во магазинов в кубе Чеки - ");
            if (ShopCount2 != null)
                inf.Append(ShopCount2.ToString());
            inf.Append(" ; </br>Сумма в кубе KPI - ");
            if (Amount1 != null)
                inf.Append(Amount1.ToString("C"));
            inf.Append(" ; </br>Сумма в кубе Чеки - ");
            if (Amount2 != null)
                inf.Append(Amount2.ToString("C"));

            if (ShopCount1 == null || ShopCount2 == null || ShopCount1 == 0 || ShopCount2 == 0)
            {
                ErrCount++; bSuccess = false;
                if (ErrCount > 0)
                {
                    if (ErrCount == 1)
                        LogTitle = "";
                    else
                        LogTitle += "; ";
                    LogTitle += "Данные не подтверждены."; // Ошибка - Данные BI не обновлены
                }
                else
                    LogTitle = "Данные не подтверждены.";
            }
            else
            {

                if (ShopCount1 != ShopCount2)
                {
                    ErrCount++; bSuccess = false;
                    if (ErrCount > 0)
                    {
                        if (ErrCount == 1)
                            LogTitle = "";
                        else
                            LogTitle += "; ";
                        LogTitle += "Данные не подтверждены."; // Ошибка - Отсутствуют данные по магазинам
                    }
                    else
                        LogTitle = "Данные не подтверждены."; // Ошибка - Отсутствуют данные по магазинам
                }

                Decimal ddd = Math.Abs(Amount1 - Amount2);
                Decimal proc1 = 0;
                if (Amount1 > 0)
                    proc1 = (ddd / Amount1) * 100;
                Decimal proc2 = 0;
                if (Amount2 > 0)
                    proc2 = (ddd / Amount2) * 100;

                if (proc1 > 2 || proc2 > 2)
                {
                    ErrCount++; bSuccess = false;
                    if (ErrCount > 0)
                    {
                        if (ErrCount == 1)
                            LogTitle = "";
                        else
                            LogTitle += "; ";
                        LogTitle += "Данные не подтверждены."; // Данные не подтверждены.
                    }
                    else
                        LogTitle += "Данные не подтверждены."; // Данные не подтверждены.
                }
            }

            notes.Append("Кол-во ошибок- " + ErrCount.ToString() + "</br> ; ");
            notes.Append(inf.ToString());
            InserToLog(LogTitle, DateTime.Now, bSuccess, ErrCount, notes.ToString(), EventTymeStr, WarningsCount);

        }
        #endregion Job Methods

        #region Cube

        Exception GetKPIData(ref CellSet cst, ref int parShopCount, ref Decimal parAmount
    , int parYear, int parMonth, int parDay)
        {
            Exception ret = null;
            CellSet cst0 = null;
            string strConn1 = _connStr;
            string str1 = string.Format("{0:0000}{1:00}", parYear, parMonth);
            string str2 = string.Format("{0:00}.{1:00}.{2:0000}", parDay, parMonth, parYear);
            string strMDX1 =
@"
SELECT
{ [Measures].[Кол-во магазинов в ДП], [Measures].[Оборот Все Руб] }
ON COLUMNS,
-- HIERARCHIZE( { [Дата].[Месяцы].&[" + str1 + @"] } ) * { [Дата].[День].[День].ALLMEMBERS }
HIERARCHIZE( { [Дата].[День].[" + str2 + @"] } )
ON ROWS
FROM [KPI]
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR
";

            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (AdomdConnection conn1 = new AdomdConnection(strConn1))
                    {
                        conn1.Open();
                        using (AdomdCommand cmd = new AdomdCommand(strMDX1, conn1))
                        {
                            cst0 = cmd.ExecuteCellSet();
                            conn1.Close();
                        }
                    }
                });
            }
            catch (Exception err)
            {
                cst = null;
                ret = new Exception(err.Message, err);
            }

            if (ret == null)
            {
                cst = cst0;
                GetValuesInCells(ref cst, ref parShopCount, ref parAmount);
            }

            return ret;
        }

        Exception GetCheckData(ref CellSet cst, ref int parShopCount, ref Decimal parAmount,
            int parYear, int parMonth, int parDay)
        {
            Exception ret = null;
            CellSet cst0 = null;
            string strConn1 = _connStr;
            string str1 = string.Format("{0:0000}{1:00}", parYear, parMonth);
            string str2 = string.Format("{0:00}.{1:00}.{2:0000}", parDay, parMonth, parYear);
            string strMDX1 =
@"
SELECT
{ [Measures].[Кол-во магазинов в ДП], [Measures].[Оборот руб] }
ON COLUMNS,
HIERARCHIZE( { [Дата].[День].[" + str2 + @"] } )
ON ROWS
FROM [Чеки]
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR
";

            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (AdomdConnection conn1 = new AdomdConnection(strConn1))
                    {
                        conn1.Open();
                        using (AdomdCommand cmd = new AdomdCommand(strMDX1, conn1))
                        {
                            cst0 = cmd.ExecuteCellSet();
                            conn1.Close();
                        }
                    }
                });
            }
            catch (Exception err)
            {
                cst = null;
                ret = new Exception(err.Message, err);
            }
            if (ret == null)
            {
                cst = cst0;
                GetValuesInCells(ref cst, ref parShopCount, ref parAmount);
            }
            return ret;
        }

        Exception InserToLog(string parName, DateTime parDT, bool parSuccess, int parErrCount
            , string parLogText, string parEventType, int parWarningsCount)
        {
            Exception ret = null;
            try
            {
                using (SPSite site = new SPSite("http://localhost/BI/"))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists["Системный журнал"];
                        SPListItem item = list.AddItem();
                        item["Title"] = parName;
                        item["Дата"] = parDT;
                        item["Успех"] = parSuccess;
                        item["КолВо Ошибок"] = parErrCount;
                        item["Журнал"] = parLogText;
                        item["Тип События"] = parEventType;
                        item["Внимание"] = parWarningsCount;

                        item.Update();
                    }
                }
            }
            catch (Exception err)
            {
                ret = new Exception(err.Message, err);
            }

            return ret;
        }

        Exception GetValuesInCells(ref CellSet cst, ref int parShopCount, ref Decimal parAmount)
        {
            Exception ret = null;
            //check if any axes were returned else throw error.
            int axes_count = cst.Axes.Count;
            if (axes_count == 0)
                return new Exception("No data returned for the selection");

            //if axes count is not 2
            if (axes_count != 2)
                return new Exception("The sample code support only queries with two axes");

            //if no position on either row or column throw error
            if (!(cst.Axes[0].Positions.Count > 0) && !(cst.Axes[1].Positions.Count > 0))
                return new Exception("No data returned for the selection");

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

            //Number of dimensions on the column
            col_dim_count = cst.Axes[0].Positions[0].Members.Count;

            //Number of dimensions on the row
            if (cst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = cst.Axes[1].Positions[0].Members.Count;

            //Total rows and columns
            row_count = cst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            col_count = cst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            for (cur_row = 0; cur_row < row_count; cur_row++)
            {
                for (cur_col = 0; cur_col < col_count; cur_col++)
                {
                    //check if we are writing to a ROW having column header
                    if (cur_row < col_dim_count)
                    {
                        //check if we are writing to a cell having row header
                        if (cur_col < row_dim_count)
                        {
                            //this should be empty cell -- it's on top left of the grid.
                        }
                        else
                        {
                            //this is a column header cell -- use member caption for header
                            //lbl.Text = cst.Axes[0].Positions[cur_col - row_dim_count].Members[cur_row].Caption;
                        }
                    }
                    else
                    {
                        //We are here.. so we are writing a row having data (not column headers)

                        //check if we are writing to a cell having row header
                        if (cur_col < row_dim_count)
                        {
                            //this is a row header cell -- use member caption for header
                            //lbl.Text = cst.Axes[1].Positions[cur_row - col_dim_count].Members[cur_col].Caption;
                        }
                        else
                        {
                            //this is data cell.. so we write the Formatted value of the cell.
                            //lbl.Text = cst[cur_col - row_dim_count, cur_row - col_dim_count].FormattedValue + "&nbsp;";
                            if (cur_row == 1 && cur_col == 1)
                            { // Shop count
                                parShopCount = Convert.ToInt32(cst[cur_col - row_dim_count, cur_row - col_dim_count].Value);
                            }
                            if (cur_row == 1 && cur_col == 2)
                            { // Amount
                                parAmount = Convert.ToDecimal(cst[cur_col - row_dim_count, cur_row - col_dim_count].Value);
                            }
                        }

                    }
                }
            }
            return ret;
        }

        #endregion Cube
    }
}
